import os
import re
import threading
import tkinter as tk
import pandas as pd
import logOutput

from datetime import datetime
from tkinter import filedialog
from tkinter import messagebox
from tkinter import ttk
from openpyxl.styles import Alignment
from openpyxl.utils import get_column_letter
from tkcalendar import DateEntry
from SnowFlake import Snowflake


class ExcelProcessorApp:
    def generate_snowflake_id(snowflake):
        return str(snowflake.next_id())
    def extract_date(text):
        date_pattern = r'\d{1,4}\d{1,2}\d{1,4}'
        matches = re.findall(date_pattern, text)
        return matches[0]

    def generate_next_id(current_id):
        # 分离出前缀和数字部分
        prefix, number_str = current_id.split('0', 1)
        number = int(number_str)
        next_number = number + 1
        next_number_str = format(next_number, '012d')
        next_id = prefix + '0' * (12 - len(next_number_str)) + next_number_str
        return next_id
    def convert_seconds_to_dhms(total_seconds, multiplier=0.36):
        adjusted_seconds = total_seconds * multiplier
        days = int(adjusted_seconds // 86400)
        remainder = adjusted_seconds % 86400
        hours = int(remainder // 3600)
        remainder = remainder % 3600
        minutes = int(remainder // 60)
        seconds = int(remainder % 60)
        formatted_time = f"+ {days}d {hours}h {minutes}m {seconds}s"
        return formatted_time
    def format_excel(filePath, df):
        # 创建一个 ExcelWriter 对象
        with pd.ExcelWriter(filePath, engine='openpyxl') as writer:
            # 将数据写入 Excel，但不包括表头
            df.to_excel(writer, sheet_name='Sheet1', index=False)
            ws = writer.sheets['Sheet1']
            # 创建一个居中的对齐对象
            alignment = Alignment(horizontal='center', vertical='center', wrapText=True)

            # 遍历表头单元格并设置居中格式
            for cell in ws[1]:
                # 假设表头在第一行
                cell.alignment = alignment
            width = 3  # 手动加宽的数值
            # 单元格列宽处理
            dims = {}
            for row in ws.rows:
                for cell in row:
                    if cell.value:
                        cell_len = 0.7 * len(re.findall('([\u4e00-\u9fa5])', str(cell.value))) + len(str(cell.value))
                        dims[cell.column] = max((dims.get(cell.column, 0), cell_len))
                for col, value in dims.items():
                    ws.column_dimensions[get_column_letter(col)].width = value + width

    def __init__(self, root):
        current_directory = os.getcwd()
        parent_directory  = os.path.dirname(current_directory)
        LogOutputPath = current_directory + '\\log'
        if not os.path.exists(LogOutputPath):
            os.makedirs(LogOutputPath)
        logOutput.writeLog(LogOutputPath + '\\process')
        logOutput.logger.info('*********** {}'.format("The process is beginning!***********"))
        self.baseDir = parent_directory
        self.root = root
        self.root.title("Excel Processing")
        # 创建一个bg Style 并设置背景颜色
        bg_style = ttk.Style()
        bg_style.configure('My.TFrame', background='#E8F5E9')  # 浅绿色背景
        main_frame = ttk.Frame(root, padding="10", style='My.TFrame')
        main_frame.pack(fill="both", expand=True, padx=10, pady=10)
        main_frame.grid(row=0, column=0, sticky=(tk.W, tk.E, tk.N, tk.S))
        # 添加标题
        title_label = ttk.Label(main_frame, text="Processing Excel Files", foreground="black", font=("Helvetica", 16, "bold"))
        title_label.grid(row=0, column=0, columnspan=3, pady=10)

        # 获取当前日期
        today = datetime.now().date()

        # 使用DateEntry，它是Calendar的一个简单封装
        self.date_label = ttk.Label(main_frame, text="Select Date:", foreground="black", font=("Helvetica", 10, "bold"))
        self.date_label.grid(row=1, column=0, sticky=tk.W, pady=5)
        # 注意这里 DateEntry 的 row 也是 1，并且 column 是 1
        self.date_entry = DateEntry(main_frame, width=47, year=today.year, month=today.month, day=today.day)
        self.date_entry.grid(row=1, column=1, pady=5)
        logOutput.logger.info(' {}'.format("Calendar Init successfully"))

        # 输入目录选择
        self.input_label = ttk.Label(main_frame, text="Input Directory:", foreground="black", font=("Helvetica", 10, "bold"))
        self.input_label.grid(row=2, column=0, sticky=tk.W, pady=5)
        self.input_value = tk.StringVar()
        self.input_value.set(self.baseDir + "\\Input")
        self.input_entry = ttk.Entry(main_frame, width=50, textvariable=self.input_value)
        self.input_entry.grid(row=2, column=1, pady=5)
        style = ttk.Style()
        style.configure('My.TButton', foreground='blue', background='lightyellow', font=('Helvetica', 10, 'bold'))
        self.input_button = ttk.Button(main_frame, text="Browse", command=self.browse_input, style = 'My.TButton')
        self.input_button.grid(row=2, column=2, pady=5)
        logOutput.logger.info(' {}'.format("Input Dir Init successfully"))

        # 输出目录选择
        self.output_label = ttk.Label(main_frame, text="Output Directory:", foreground="black", font=("Helvetica", 10, "bold"))
        self.output_label.grid(row=3, column=0, sticky=tk.W, pady=5)
        self.output_value = tk.StringVar()
        self.output_value.set(self.baseDir + "\\Output")
        self.output_entry = ttk.Entry(main_frame, width=50, textvariable=self.output_value)
        self.output_entry.grid(row=3, column=1, pady=5)
        self.output_button = ttk.Button(main_frame, text="Browse", command=self.browse_output, style = 'My.TButton')
        self.output_button.grid(row=3, column=2, pady=5)
        logOutput.logger.info(' {}'.format("Output Dir Init successfully"))

        # 进度条
        self.progress = ttk.Progressbar(main_frame, orient="horizontal", length=400, mode="determinate", maximum=100)
        self.progress.pack_forget()

        # 状态标签
        self.label = ttk.Label(main_frame, text="")
        self.label.grid(row=5, column=0, columnspan=3, pady=5)

        # 开始按钮
        self.start_button = ttk.Button(main_frame, text="Start", command=self.start_processing, style = 'My.TButton')
        self.start_button.grid(row=6, column=0, columnspan=3, pady=5)

    def browse_input(self):
        directory = filedialog.askdirectory()
        if directory:
            self.input_entry.delete(0, tk.END)
            self.input_entry.insert(0, directory)

    def browse_output(self):
        directory = filedialog.askdirectory()
        if directory:
            self.output_entry.delete(0, tk.END)
            self.output_entry.insert(0, directory)

    def start_processing(self):
        try:
            self.label.config(text="Please wait...")
            logOutput.logger.info(' {}'.format("start processing......"))
            self.start_button.config(state=tk.DISABLED)
            self.thread = threading.Thread(target=self.process_files)
            self.thread.start()
            self.root.after(100, self.check_thread)
        except Exception as e:
            messagebox.showerror("Alert!", str(e))
            logOutput.logger.error(' {}'.format(e))

    def process_files(self):
        global formatted_date
        input_dir = self.input_entry.get()
        output_dir = self.output_entry.get()
        # 判断是否选择文件
        if not input_dir or not output_dir :
            messagebox.showinfo("Alert!", "Please provide both input and output directories.")
            logOutput.logger.error(' {}'.format('Please provide both input and output directories.'))
            return
        self.label.config(text = 'Loading...')
        self.root.update_idletasks()
        # 获取DC列表
        dc_input_dir_path = os.path.join(input_dir, "OMP DC.xlsx")
        if not os.path.exists(dc_input_dir_path):
            messagebox.showerror("Alert!", "File OMP DC.xlsx does not exist! ")
            logOutput.logger.error(' {}'.format('File OMP DC.xlsx does not exist!'))
            return
        dc_input_df = pd.read_excel(dc_input_dir_path, engine='openpyxl')
        dc_values = dc_input_df["DC"].astype(str).unique()
        logOutput.logger.info(' {}'.format('Get DC List successfully'))
        # 获取dc_rebalancing列表
        dc_rebalancing_input_dir_path = os.path.join(input_dir, "DC Rebalancing.xlsx")
        if not os.path.exists(dc_rebalancing_input_dir_path):
            messagebox.showerror("Alert!", "File DC Rebalancing.xlsx does not exist! ")
            logOutput.logger.error(' {}'.format('File DC Rebalancing.xlsx does not exist! '))
            return
        dc_rebalancing_input_df = pd.read_excel(dc_rebalancing_input_dir_path, engine='openpyxl')
        dc_rebalancing_input_df['key'] = list(zip(dc_rebalancing_input_df['FROM'], dc_rebalancing_input_df['TO']))
        logOutput.logger.info(' {}'.format('Get DR Rebalance List successfully'))
        # 获取ZC564列表
        zc564_input_dir_path = os.path.join(input_dir, "ZC564.xls")
        if not os.path.exists(zc564_input_dir_path):
            messagebox.showerror("Alert!", "File ZC564.xls does not exist! ")
            logOutput.logger.error(' {}'.format('File ZC564.xls does not exist! '))
            return
        zc564_input_df = pd.read_excel(zc564_input_dir_path)
        zc564_values = zc564_input_df["MRP Controller"].unique()
        logOutput.logger.info(' {}'.format('Get zc564 MRP Controller successfully'))
         # 获取APO列表
        apo_input_dir_path = os.path.join(input_dir, "APO Transportation Lane")
        if not os.path.exists(apo_input_dir_path):
            messagebox.showerror("Alert!", "Directory APO Transportation Lane does not exist! ")
            logOutput.logger.error(' {}'.format('Directory APO Transportation Lane does not exist!'))
            return
        selected_date = self.date_entry.get_date()
        if selected_date is not None:
            # 将日期对象格式化为 YYYYMMDD 格式的字符串
            formatted_date = selected_date.strftime('%Y%m%d')
        apo_input_files = [
            f for f in os.listdir(apo_input_dir_path)
            if (f.lower().endswith('.xlsx') or f.lower().endswith('.xls'))
               and formatted_date in f
        ]
        total_files = len(apo_input_files)
        if total_files == 0 :
            messagebox.showerror("Alert!", "File APO Transportation Lane YYYYMMDD.xlsx does not exist! ")
            logOutput.logger.error(' {}'.format('File APO Transportation Lane YYYYMMDD.xlsx does not exist! '))
            return
        logOutput.logger.info(' {}'.format('Get APO Transportation Lane Data List successfully'))
        for idx, filename in enumerate(apo_input_files):
            apo_input_file_path = os.path.join(apo_input_dir_path, filename)
            apo_input_df = pd.read_excel(apo_input_file_path, engine='openpyxl')
            # 过滤Source与Plant列等于DC值
            apo_matched_df = apo_input_df[(apo_input_df['Source'].isin(dc_values)) & (apo_input_df['Plant'].isin(dc_values))]
            newfilename = filename.split(".")[0]
            suffix = filename.split(".")[1]
            # 获取日期
            fileDateName = ExcelProcessorApp.extract_date(filename)
            # APO hair源数据处理
            hair_apo_output_dir = output_dir + "/Hair/" + fileDateName
            if not os.path.exists(hair_apo_output_dir):
                os.makedirs(hair_apo_output_dir)
            hairFileName =  "Hair " + newfilename + "." + suffix
            output_hair_apo_file_path = os.path.join(hair_apo_output_dir, hairFileName)
            hair_apo_matched_df = apo_matched_df[apo_matched_df['Category'] == "HAIRCARE"]
            try:
                hair_apo_matched_df.to_excel(output_hair_apo_file_path, index=False, engine='openpyxl')
                logOutput.logger.info(' {}'.format('output hair apo file successfully'))
            except Exception as e:
                messagebox.showerror('Alert!', e)
                logOutput.logger.error(' {}'.format(e))
                return
            self.progress.grid(row=4, column=0, columnspan=3, pady=10)
            self.progress['value'] = 15
            self.label.config(text = 'Hair APO Transportation Lane successfully')
            self.root.update_idletasks()
            # APO PCC 源数据处理
            pcc_apo_output_dir = output_dir + "/PCC/" + fileDateName
            if not os.path.exists(pcc_apo_output_dir):
                os.makedirs(pcc_apo_output_dir)
            pccFileName = "PCC " + newfilename + "." + suffix
            pcc_apo_matched_df = apo_matched_df[apo_matched_df['Category'] == "PCC"]
            output_pcc_apo_file_path = os.path.join(pcc_apo_output_dir, pccFileName)
            try:
                pcc_apo_matched_df.to_excel(output_pcc_apo_file_path, index=False, engine='openpyxl')
                logOutput.logger.info(' {}'.format('output ppc apo file successfully'))
            except Exception as e:
                messagebox.showerror('Alert!', e)
                logOutput.logger.error(' {}'.format(e))
                return
            self.progress['value'] =  30
            self.label.config(text = 'PCC APO Transportation Lane successfully')
            self.root.update_idletasks()
            # Hair BU REFTransport
            hair_apo_input_df = pd.read_excel(output_hair_apo_file_path, engine='openpyxl')
            new_rows = []
            snowflake = Snowflake(data_center_id=0, worker_id=0)
            for index, row in hair_apo_input_df.iterrows():
                today = datetime.now().strftime('%Y%m%d%H%M%S')
                # 将Valid From Date (TRPROD)和Valid To Date (TRPROD)列转换为字符串
                fromDate = row['Valid From Date (TRPROD)']
                toDate = row['Valid To Date (TRPROD)']
                # 筛选条件：Valid From Date (TRPROD)大于今天 或 Valid To Date (TRPROD)不等于'9999*'
                flag = (fromDate > int(today)) | (str(toDate).startswith('9999') == False)
                comments=  ''
                if flag: comments = 'Sourcing change from ' + str(fromDate)[:-6]
                new_row = {
                    'TransportId': 'H' + ExcelProcessorApp.generate_snowflake_id(snowflake),
                    'FromLocationId': row['Source'],
                    'ToLocationId': row['Plant'],
                    'ProductId': str(row['Material']),
                    'Comments': comments,
                    'Tlane LT': row['Tlane LT'],
                    'FromDate': str(fromDate)[:-6],
                    'PDT': '',
                    'GR': ''
                }
                new_rows.append(new_row)
            hair_apo_input_df['key'] = list(zip(hair_apo_input_df['Source'], hair_apo_input_df['Plant']))
            # 获取所有Froms
            froms = dc_rebalancing_input_df["FROM"].unique()
            # 获取所有keys
            keys = dc_rebalancing_input_df["key"].unique()
            # 获取所有Froms的apo
            merged_source_df = hair_apo_input_df[(hair_apo_input_df['Source'].isin(froms))]
            # 获取所有keys的apo
            merged_key_df = hair_apo_input_df[(hair_apo_input_df['key'].isin(keys))]
            # 根据key Material 去重复
            merged_key_df = merged_key_df.drop_duplicates(subset=['key', 'Material'], keep='first')
            # 根据Source Material 去重复
            merged_source_df = merged_source_df.drop_duplicates(subset=['Source', 'Material'], keep='first')
            merged_source_df = merged_source_df[hair_apo_input_df.columns]
            for index, row in merged_source_df.iterrows():
                rows_with_key = dc_rebalancing_input_df.loc[dc_rebalancing_input_df['FROM'] == row['Source']].iloc[0]
                is_present = row['Material'] in merged_key_df['Material'].values
                if (is_present): continue
                fromDate = row['Valid From Date (TRPROD)']
                new_row = {
                    'TransportId': 'H' + ExcelProcessorApp.generate_snowflake_id(snowflake),
                    'FromLocationId': row['Source'],
                    'ToLocationId': rows_with_key['TO'],
                    'ProductId': str(row['Material']),
                    'Comments': 'DC Rebalancing',
                    'Tlane LT': '',
                    'FromDate': str(fromDate)[:-6],
                    'PDT': rows_with_key['PDT'],
                    'GR': rows_with_key['GR']
                }
                new_rows.append(new_row)
            hair_REFTransport_df = pd.DataFrame(new_rows)
            hair_REFTransport_output_dir = output_dir + "/Hair/" + fileDateName
            output_hair_REFTransport_file_path = os.path.join(hair_REFTransport_output_dir, "Hair REFTransport " + fileDateName + ".xlsx")
            try:
                ExcelProcessorApp.format_excel(output_hair_REFTransport_file_path, hair_REFTransport_df.drop(columns=['Tlane LT', 'FromDate', 'PDT', 'GR']))
                logOutput.logger.info(' {}'.format('output hair REFTransport file successfully'))
            except Exception as e:
                messagebox.showerror('Alert!', e)
                logOutput.logger.error(' {}'.format(e))
                return
            self.progress['value'] = 40
            self.label.config(text = 'Hair REFTransport successfully')
            self.root.update_idletasks()
            # PCC BU REFTransport
            pcc_apo_input_df = pd.read_excel(output_pcc_apo_file_path, engine='openpyxl')
            new_rows = []
            for index, row in pcc_apo_input_df.iterrows():
                today = datetime.now().strftime('%Y%m%d%H%M%S')
                # 将Valid From Date (TRPROD)和Valid To Date (TRPROD)列转换为字符串
                fromDate = row['Valid From Date (TRPROD)']
                toDate = row['Valid To Date (TRPROD)']
                # 筛选条件：Valid From Date (TRPROD)大于今天 或 Valid To Date (TRPROD)不等于'9999*'
                flag = (fromDate > int(today)) | (str(toDate).startswith('9999') == False)
                comments=  ''
                if flag: comments = 'Sourcing change from ' + str(fromDate)[:-6]
                new_row = {
                    'TransportId': 'P' + ExcelProcessorApp.generate_snowflake_id(snowflake),
                    'FromLocationId': row['Source'],
                    'ToLocationId': row['Plant'],
                    'ProductId': str(row['Material']),
                    'Comments': comments,
                    'Tlane LT': row['Tlane LT'],
                    'FromDate': str(fromDate)[:-6],
                    'PDT': '',
                    'GR': ''
                }
                new_rows.append(new_row)
            pcc_REFTransport_df = pd.DataFrame(new_rows)
            pcc_REFTransport_output_dir = output_dir + "/PCC/" + fileDateName
            output_pcc_REFTransport_file_path = os.path.join(pcc_REFTransport_output_dir, "PCC REFTransport " + fileDateName + ".xlsx")
            try:
                ExcelProcessorApp.format_excel(output_pcc_REFTransport_file_path, pcc_REFTransport_df.drop(columns=['Tlane LT', 'FromDate', 'PDT', 'GR']))
                logOutput.logger.info(' {}'.format('output pcc REFTransport file successfully'))
            except Exception as e:
                messagebox.showerror('Alert!', e)
                logOutput.logger.error(' {}'.format(e))
                return
            self.progress['value'] = 50
            self.label.config(text = 'PCC REFTransport successfully')
            self.root.update_idletasks()
            # Hair REFTransportType
            new_rows = []
            for index, row in hair_REFTransport_df.iterrows():
                if row['Comments'] == 'DC Rebalancing':
                    TransportOffset = row['PDT'] * 86400
                    GRPOffset = row['GR'] * 86400
                    TLaneType = 'Rebalance'
                else:
                    TransportOffset = row['Tlane LT'] * 0.36
                    GRPOffset = ''
                    TLaneType = ''
                new_row = {
                    'TransportId': row['TransportId'],
                    'TransportType': 'Default',
                    'TLaneType': TLaneType,
                    'Preference': '10',
                    'Emergency': 'NO',
                    'TransportOffset': TransportOffset,
                    'GRPOffset': GRPOffset,
                    'ExpCalendarId': '',
                    'TransportCalendarId': '',
                    'RecepCalendarId': ''
                }
                new_rows.append(new_row)
            hair_REFTransport_type_df = pd.DataFrame(new_rows)
            hair_REFTransport_type_output_dir = output_dir + "/Hair/" + fileDateName
            output_hair_REFTransport_type_file_path = os.path.join(hair_REFTransport_type_output_dir, "Hair REFTransportType " + fileDateName + ".xlsx")
            try:
                ExcelProcessorApp.format_excel(output_hair_REFTransport_type_file_path, hair_REFTransport_type_df)
                logOutput.logger.info(' {}'.format('output hair REFTransportType file successfully'))
            except Exception as e:
                messagebox.showerror('Alert!', e)
                logOutput.logger.error(' {}'.format(e))
                return
            self.progress['value'] = 60
            self.label.config(text = 'Hair REFTransportType successfully')
            self.root.update_idletasks()
            # PCC REFTransportType
            new_rows = []
            for index, row in pcc_REFTransport_df.iterrows():
                new_row = {
                    'TransportId': row['TransportId'],
                    'TransportType': 'Default',
                    'TLaneType': '',
                    'Preference': '10',
                    'Emergency': 'NO',
                    'TransportOffset': row['Tlane LT'] * 0.36,
                    'GRPOffset': '',
                    'ExpCalendarId': '',
                    'TransportCalendarId': '',
                    'RecepCalendarId': ''
                }
                new_rows.append(new_row)
            pcc_REFTransport_type_df = pd.DataFrame(new_rows)
            pcc_REFTransport_type_output_dir = output_dir + "/PCC/" + fileDateName
            output_pcc_REFTransport_type_file_path = os.path.join(pcc_REFTransport_type_output_dir, "PCC REFTransportType " + fileDateName + ".xlsx")
            try:
                ExcelProcessorApp.format_excel(output_pcc_REFTransport_type_file_path, pcc_REFTransport_type_df)
                logOutput.logger.info(' {}'.format('output pcc REFTransport Type file successfully'))
            except Exception as e:
                messagebox.showerror('Alert!', e)
                logOutput.logger.error(' {}'.format(e))
                return
            self.progress['value'] =  70
            self.label.config(text = 'PCC REFTransportType successfully')
            self.root.update_idletasks()
            # BU Sourcing Change
            new_rows = []
            for index, row in hair_REFTransport_df[hair_REFTransport_df['Comments'].str.contains('Sourcing change', na=False)].iterrows():
                PrefProcessId = 'TR/' + str(row['ProductId']) + '/' + row['ToLocationId'] + '/' + row['FromLocationId'] + '/Default'
                ProductLocationId = str(row['ProductId']) + '/' + row['ToLocationId']
                new_row = {
                    'SourcingDecisionId': 'HSC' + ExcelProcessorApp.generate_snowflake_id(snowflake),
                    'Active': 'YES',
                    'COMMENTS': '',
                    'DataOrigin': 'DB',
                    'FromDate': row['FromDate'],
                    'InactiveReason': '',
                    'IsActive': 'YES',
                    'PID': 'DCs',
                    'MURESPONSIBLEGROUPID': zc564_values[0],
                    'PartitionId': 'DCs',
                    'Preference': '-1',
                    'PrefProcessId': PrefProcessId,
                    'ProductLocationId': ProductLocationId,
                    'StatusIcon': '',
                    'StatusMessage': '',
                    'APPLICATION': 'PLS',
                    'ObjectStatus': 'new',
                    'SID': '',
                    'SOURCE': 'ProjectSpecific',
                    'TXID': ''
                }
                new_rows.append(new_row)
            hair_sourcing_change_df = pd.DataFrame(new_rows)
            hair_sourcing_change_output_dir = output_dir + "/Hair/" + fileDateName
            output_hair_sourcing_change_file_path = os.path.join(hair_sourcing_change_output_dir, "Hair Sourcing Change " + fileDateName + ".xlsx")
            try:
                ExcelProcessorApp.format_excel(output_hair_sourcing_change_file_path, hair_sourcing_change_df)
                logOutput.logger.info(' {}'.format('output hair sourcing_change file successfully'))
            except Exception as e:
                messagebox.showerror("Alert!", e)
                logOutput.logger.error(' {}'.format(e))
                return
            self.progress['value'] = 85
            self.label.config(text = 'Hair Sourcing change successfully')
            self.root.update_idletasks()
            new_rows = []
            for index, row in pcc_REFTransport_df[pcc_REFTransport_df['Comments'].str.contains('Sourcing change', na=False)].iterrows():
                PrefProcessId = 'TR/' + str(row['ProductId']) + '/' + row['ToLocationId'] + '/' + row['FromLocationId'] + '/Default'
                ProductLocationId = str(row['ProductId']) + '/' + row['ToLocationId']
                new_row = {
                    'SourcingDecisionId': 'PSC' + ExcelProcessorApp.generate_snowflake_id(snowflake),
                    'Active': 'YES',
                    'COMMENTS': '',
                    'DataOrigin': 'DB',
                    'FromDate': row['FromDate'],
                    'InactiveReason': '',
                    'IsActive': 'YES',
                    'PID': 'DCs',
                    'MURESPONSIBLEGROUPID': zc564_values[0],
                    'PartitionId': 'DCs',
                    'Preference': '-1',
                    'PrefProcessId': PrefProcessId,
                    'ProductLocationId': ProductLocationId,
                    'StatusIcon': '',
                    'StatusMessage': '',
                    'APPLICATION': 'PLS',
                    'ObjectStatus': 'new',
                    'SID': '',
                    'SOURCE': 'ProjectSpecific',
                    'TXID': ''
                }
                new_rows.append(new_row)
            pcc_sourcing_change_df = pd.DataFrame(new_rows)
            pcc_sourcing_change_output_dir = output_dir + "/PCC/" + fileDateName
            output_pcc_sourcing_change_file_path = os.path.join(pcc_sourcing_change_output_dir, "PCC Sourcing Change " + fileDateName + ".xlsx")
            try:
                ExcelProcessorApp.format_excel(output_pcc_sourcing_change_file_path, pcc_sourcing_change_df)
                logOutput.logger.info(' {}'.format('output pcc sourcing_change file successfully'))
            except Exception as e:
                messagebox.showerror("Alert!", e)
                logOutput.logger.error(' {}'.format(e))
                return
            self.progress['value'] = 100
            self.label.config(text = 'PCC Sourcing change successfully')
            self.root.update_idletasks()
            self.label.config(text="Processing Complete")
            self.root.update_idletasks()
            # 弹出确认对话框
            result = messagebox.askyesno("Confirmation", "All processing is done. Open output directory?")
            if result:
                # 如果用户点击了"是"，则打开目录
                os.startfile(output_dir)
                root.withdraw()  # 隐藏主窗口
    def check_thread(self):
        if self.thread.is_alive():
            self.root.after(100, self.check_thread)
        else:
            self.start_button.config(state=tk.NORMAL)
            self.label.config(text="Processing Complete")

if __name__ == "__main__":
    root = tk.Tk()
    app = ExcelProcessorApp(root)
    root.mainloop()
